use documents
go

drop table DocumentType_Category
go

drop table DocumentSet
go

drop table CareEvent
go

if exists (select * from sysobjects where name='Category' and xtype='U')
	drop table Category
go
create table Category
(
	CategoryID UNIQUEIDENTIFIER primary key default newid(),
	CategoryName nvarchar(255),
	CategoryParentID UNIQUEIDENTIFIER NULL
)
insert Category(CategoryID,CategoryName) values('D467B56E-434C-4F6E-8037-0A3E4629E3F0','Summaries & care plans')
insert Category(CategoryID,CategoryName) values('5D96662A-B9CA-4490-A391-0A42AA478D4F','MDT records')
insert Category(CategoryID,CategoryName) values('EBBEDE96-C0D8-43F3-83A3-18678CDF257A','Correspondence')
insert Category(CategoryID,CategoryName) values('D79BC4D3-8C9E-4BC9-8647-2E6E99B1DBAC','Clinical notes')
insert Category(CategoryID,CategoryName) values('6F349903-630D-442D-99CE-78E03813BACD','Charts, assessments & other forms')
insert Category(CategoryID,CategoryName) values('50D7F379-51C9-4945-B9CD-9CED70DC859A','Tests')
insert Category(CategoryID,CategoryName) values('EC080C76-8ED8-448F-B6B9-C76ED3DBB716','Nursing')
insert Category(CategoryID,CategoryName) values('A34E2A1C-3B10-450F-9D57-EA80AD7A6342','Audit forms')

go

if exists (select * from sysobjects where name='Document' and xtype='U')
	drop table [Document]
go
if exists (select * from sysobjects where name='DocumentType' and xtype='U')
	drop table DocumentType
go

create table DocumentType
(
	DocumentTypeCode nvarchar(10) primary key not null,
	DocumentTypeName nvarchar(100)
)
go
insert DocumentType(DocumentTypeCode,DocumentTypeName) values ('DAL','Discharge advice letter')
insert DocumentType(DocumentTypeCode,DocumentTypeName) values ('NFN','Nursing filenote')
insert DocumentType(DocumentTypeCode,DocumentTypeName) values ('CLIND','Clinical document')
insert DocumentType(DocumentTypeCode,DocumentTypeName) values ('RADREQ','Radiology request')
insert DocumentType(DocumentTypeCode,DocumentTypeName) values ('URO','Urology request')
go

create table CareEvent
(
	CareEventID uniqueidentifier primary key default newid(),
	CareEventName nVarchar(255),
	CareEventDate dateTime default getdate(),
	CareEventCreator uniqueidentifier, -- The UserID
	CareEventLocation nvarchar(255) -- should be a foreign key into a Location table
)
go



insert CareEvent(CareEventID,CareEventName,CareEventCreator,CareEventLocation) values('4B76E095-B266-43F6-B05D-BEA5FA051235','Hospital Admission','4B76E095-B266-43F6-B05D-BEA5FA051235','Princess of Wales Bridgend')
insert CareEvent(CareEventID,CareEventName,CareEventCreator,CareEventLocation) values('E6D0B729-E7DD-41DF-93B9-776F66CDD1EE','Surgery Appointment','4B76E095-B266-43F6-B05D-BEA5FA051235','Portway, Porthcawl')
insert CareEvent(CareEventID,CareEventName,CareEventCreator,CareEventLocation) values('96F09558-80B0-48E8-8056-87695F130CFC','Home Visit','4B76E095-B266-43F6-B05D-BEA5FA051235','36 Hookland Road, Porthcawl')
insert CareEvent(CareEventID,CareEventName,CareEventCreator,CareEventLocation) values('16AAB9A0-6B16-4AC4-9FA4-0E291DB581A9','Emergency','4B76E095-B266-43F6-B05D-BEA5FA051235','Morriston Hospital, Swansea')


create table [Document]
(
	DocumentID uniqueidentifier primary key default newid(),
	DocumentTitle nvarchar(255),
	DocumentTypeCode nvarchar(10)  foreign key references DocumentType(DocumentTypeCode),
	DocumentAuthor nvarchar(100),
	DocumentCreationDate DateTime default getdate(),
	Revoked bit default 0,
	DocumentData nvarchar(max)
)
go


insert Document(DocumentID,DocumentTitle,DocumentTypeCode,DocumentAuthor) values('B4E1424F-AAF5-4FBC-B831-2FFC9A87422F','Discharge Advice Letter for A. Jones','DAL','Peter Phillips')
insert Document(DocumentID,DocumentTitle,DocumentTypeCode,DocumentAuthor) values('E1E0B293-9E3D-498C-8AD6-CF883717920D','Discharge Advice Letter for A. Jones (old)','DAL','Peter Whittingham')
insert Document(DocumentID,DocumentTitle,DocumentTypeCode,DocumentAuthor) values('E85C20F3-8196-4857-9438-40E1A8263BB6','A clinical document','CLIND','Selena Gomez')
insert Document(DocumentID,DocumentTitle,DocumentTypeCode,DocumentAuthor) values('D7062337-04DE-4BC4-A3E2-8D8ED92522C2','Radiology request','RADREQ','Justin Beiber')
insert Document(DocumentID,DocumentTitle,DocumentTypeCode,DocumentAuthor) values('C4FEC8D7-EEF2-4584-B26A-CD68C41BFE60','Nursing filenote','NFN','David Marshall')
go
if exists (select * from sysobjects where name='Document_Category' and xtype='U')
	drop table [Document_Category]
go
-- 
create table DocumentSet
(
	DocumentSetID  uniqueidentifier default newid(),
	DocumentID uniqueidentifier foreign key references [Document](DocumentID) on delete cascade,
	DocumentPosition smallint not null default 0, 
	CareEventID uniqueidentifier foreign key references CareEvent(CareEventID) on delete cascade,
	primary key (DocumentSetID,DocumentID)

)
go
insert DocumentSet (DocumentSetID,DocumentID,DocumentPosition,CareEventID) values ('56F7FC72-45D3-4499-BBDA-F22DCBFB3036','B4E1424F-AAF5-4FBC-B831-2FFC9A87422F',0,'4B76E095-B266-43F6-B05D-BEA5FA051235')
insert DocumentSet (DocumentSetID,DocumentID,DocumentPosition,CareEventID) values ('56F7FC72-45D3-4499-BBDA-F22DCBFB3036','E1E0B293-9E3D-498C-8AD6-CF883717920D',1,'4B76E095-B266-43F6-B05D-BEA5FA051235')

insert DocumentSet (DocumentSetID,DocumentID,DocumentPosition,CareEventID) values ('E3BAFE5F-7507-4126-8D95-A9A7D13A4F15','E85C20F3-8196-4857-9438-40E1A8263BB6',0,'96F09558-80B0-48E8-8056-87695F130CFC')
insert DocumentSet (DocumentSetID,DocumentID,DocumentPosition,CareEventID) values ('88901F28-486B-4D7C-963F-D594F7B56703','D7062337-04DE-4BC4-A3E2-8D8ED92522C2',0,'16AAB9A0-6B16-4AC4-9FA4-0E291DB581A9')
insert DocumentSet (DocumentSetID,DocumentID,DocumentPosition,CareEventID) values ('D28F6AAD-5547-43AE-9C1C-77B2F14BFBC2','C4FEC8D7-EEF2-4584-B26A-CD68C41BFE60',0,'E6D0B729-E7DD-41DF-93B9-776F66CDD1EE')
go

create table DocumentType_Category
(
	DocumentType_CategoryID uniqueidentifier primary key default newid(),
	DocumentTypeCode nvarchar(10) foreign key references DocumentType(DocumentTypeCode) on delete cascade,
	CategoryID uniqueidentifier foreign key references Category(CategoryID) on delete cascade
)

insert DocumentType_Category(DocumentType_CategoryID,DocumentTypeCode,CategoryID) values ('2EB22FB2-A38A-4B04-9FC6-1D11F36CB678','DAL','EBBEDE96-C0D8-43F3-83A3-18678CDF257A')
insert DocumentType_Category(DocumentType_CategoryID,DocumentTypeCode,CategoryID) values ('0C757075-9C16-494C-8A74-B3623B467F05','CLIND','EBBEDE96-C0D8-43F3-83A3-18678CDF257A')
insert DocumentType_Category(DocumentType_CategoryID,DocumentTypeCode,CategoryID) values ('4ABA0EE3-DC72-4103-B231-B9EAAC27DD50','RADREQ','EBBEDE96-C0D8-43F3-83A3-18678CDF257A')
insert DocumentType_Category(DocumentType_CategoryID,DocumentTypeCode,CategoryID) values ('51D7027E-DC6B-4954-9505-27CEB9D98011','NFN','D79BC4D3-8C9E-4BC9-8647-2E6E99B1DBAC')
insert DocumentType_Category(DocumentType_CategoryID,DocumentTypeCode,CategoryID) values ('D945FF5C-7A82-4C4C-8EFE-CCAC8BA9489F','CLIND','EBBEDE96-C0D8-43F3-83A3-18678CDF257A')
insert DocumentType_Category(DocumentType_CategoryID,DocumentTypeCode,CategoryID) values ('053CA54E-D795-41F4-9B2F-F546393FD7A8','CLIND','EBBEDE96-C0D8-43F3-83A3-18678CDF257A')


drop view vwDocument
go
create View vwDocument
as
	select distinct d.*, ce.* ,ds.DocumentSetID, ds.DocumentPosition, dt.DocumentTypeName, dc.CategoryID
	from document d 
	join DocumentSet ds 
	on d.documentid = ds.documentid
	join DocumentType dt on dt.DocumentTypeCode = d.DocumentTypeCode
	join DocumentType_Category dc on  d.DocumentTypeCode = dc.DocumentTypeCode
	join CareEvent ce on ds.CareEventID = ce.CareEventID
go

drop view vwDocumentHistory
go
create view vwDocumentHistory
as
	select distinct 
		DocumentID,
		DocumentTitle,
		DocumentTypeCode,
		DocumentAuthor,
		DocumentCreationDate,
		DocumentSetID,
		DocumentPosition,
		DocumentTypeName,
		Revoked
	from vwDocument
go
drop View vwHeadDocuments
go
create View vwHeadDocuments
as
	select d.* from 
		(select min(documentposition) documentposition,documentsetid from 
			(	select d.*,ds.documentposition, ds.documentsetid from document d 
				join documentset ds on d.documentid=ds.documentid
				where revoked=0
			) x
			group by documentsetid) h
	join (select d.*,ds.documentposition, ds.documentsetid from document d 
			join documentset ds on d.documentid=ds.documentid
			where revoked=0) u 
	on h.documentsetid = h.documentsetid and h.documentposition = u.documentposition
	join vwDocument d on d.documentid=u.documentid
	where h.documentsetid = u.documentsetid 
go






select * from vwHeadDocuments order by documentcreationdate
select * from Document order by documentcreationdate